﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using Top.Web;
using Top.Web.Api;
using Top.Core;
using Top.Data;
using Top.PointStore.Business.Models;

namespace Top.PointStore.Business
{
    public class PointStoreInvoiceManager
    {
        //----------------------------------------------------------添加发货清单-------------------------------------------------------
        public static async Task<int> InsertToDB(T_PointStoreInvoice model, DbTour db)
        {
            string sql = @"
                        INSERT INTO T_PointStoreInvoice
                                   (F_CityKid
                                   ,F_CityName
                                   ,F_OrderNo
                                   ,F_OrderTime
                                   ,F_ProductKid
                                   ,F_ProductName
                                   ,F_ProductCount
                                   ,F_ProductPrice
                                   ,F_OrderState
                                   ,F_LogisticsNum
                                   ,F_DeliveryTime
                                   ,F_ProductExchangeKid
                                   ,F_IsDeleted)
                             VALUES
                                   (
			                        {0:F_CityKid}            
                                   ,{0:F_CityName}          
                                   ,{0:F_OrderNo}             
                                   ,{0:F_OrderTime}           
                                   ,{0:F_ProductKid}          
                                   ,{0:F_ProductName}         
                                   ,{0:F_ProductCount}        
                                   ,{0:F_ProductPrice}        
                                   ,{0:F_OrderState}          
                                   ,{0:F_LogisticsNum}        
                                   ,{0:F_DeliveryTime}        
                                   ,{0:F_ProductExchangeKid}  
                                   ,{0:F_IsDeleted} 
                                   ) 
                            ";
            int result = 0;
            result = await db.Sql(sql, model).ExecuteNonQuery();
            return result;
        }

        //----------------------------------------------------------修改物流单号------------------------------------------------------
        public static async Task<int> UpdateToDB(string productKids, int orderState, int cityKid,string createTime ,string logisticsNum)
        {
            logisticsNum = logisticsNum.Trim();
            if (string.IsNullOrWhiteSpace(logisticsNum))
            {
                throw new WebApiException("物流单号不能为空");
            }
            string sql = @"
                    update T_PointStoreInvoice
                    set F_OrderState=1,
	                    F_LogisticsNum={0},
	                    F_DeliveryTime=now()
                    where F_ProductKid in ({3})
                        and F_OrderState={2}
                        and F_CityKid={1}
                        and F_CreateTime>={4}
                        and F_CreateTime<{5}
                    ";
            sql = string.Format(sql, "{0}", "{1}", "{2}", productKids,"{3}","{4}");
            int result = 0;
            using (var db = new DbTour())
            {
                result = await db.Sql(sql, logisticsNum, cityKid, orderState,Convert.ToDateTime(createTime),Convert.ToDateTime(createTime).AddDays(1)).ExecuteNonQuery();
            }
            return result;
        }

        public static async Task<int> UpdateToDB_Confirm(string logisticsNum,int cityKid,int productKid)
        {
            
            logisticsNum = logisticsNum.Trim();
            if (string.IsNullOrWhiteSpace(logisticsNum))
            {
                throw new WebApiException("物流单号不能为空");
            }
            if (productKid>0)
            {
                string sql = @"
                    update T_PointStoreInvoice
                    set F_OrderState=2
                    where 1=1
                        and F_LogisticsNum={0}
                        and F_OrderState=1
                        and F_CityKid={1}
                        and F_ProductKid = {2}
                    ";
                using (var db = new DbTour())
                {
                    return await db.Sql(sql, logisticsNum, cityKid, productKid).ExecuteNonQuery();
                }
            }
            else
            {
                string sql = @"
                    update T_PointStoreInvoice
                    set F_OrderState=2
                    where 1=1
                        and F_LogisticsNum={0}
                        and F_OrderState=1
                        and F_CityKid={1}
                    ";
                using (var db = new DbTour())
                {
                    return await db.Sql(sql, logisticsNum, cityKid).ExecuteNonQuery();
                }
            }

        }

        public static async Task<int> UpdateToDB_Backout(int productKid,int cityKid, string createTime,int num = 0)
        {
            using (var db = new DbTour())
            {
                string sql = @"
                    SELECT
	                    Kid
                    FROM
	                    t_pointstoreinvoice
                    WHERE
	                    1 = 1
                    AND F_CityKid = {0}
                    AND F_ProductKid = {1}
                    AND F_CreateTime >= {2}
                    AND F_CreateTime < {3}
                    AND F_OrderState = 0
                    AND F_IsDeleted=0
                    LIMIT {4}
                    ";
                List<int> kids =await db.Sql(sql, cityKid, productKid, Convert.ToDateTime(createTime), Convert.ToDateTime(createTime).AddDays(1), num).ToList<int>();
                if (kids.Count>0)
                {
                    StringBuilder sb = new StringBuilder();
                    foreach (int item in kids)
                    {
                        sb.Append("," + item);
                    }
                    string inKid = sb.ToString().Substring(1);
                    string sql2 = @"
                        update t_pointstoreinvoice
                        set f_isdeleted = 1
                        where kid in("+inKid+")";
                    await db.Sql(sql2).ExecuteNonQuery();
                }
                return 1;
            }            
        }

        public static async Task<int> UpdateToDB_LogisticsNum(string oldnum, string newnum,int cityKid)
        {
            string sql = @"
            update t_pointstoreinvoice
            set F_LogisticsNum = {1}
            where F_LogisticsNum = {0}";
            if (cityKid>0&&cityKid!=999)
            {
                sql = @"
            update t_pointstoreinvoice
            set F_LogisticsNum = {1}
            where F_LogisticsNum = {0}
            and f_cityKid="+cityKid;
            }
            using (var db = new DbTour())
            {
               return await db.Sql(sql, oldnum, newnum).ExecuteNonQuery();
            }
        }
        public static async Task<int> Shipments(T_PointStoreInvoice model,int top=1)
        {
            string sql = @"
                update T_PointStoreInvoice
                set F_OrderState=1,
                    F_LogisticsNum = {1},
                    F_DeliveryTime = now()
                where 1=1
                and kid in({0})";
            string sql1 = @"SELECT kid FROM T_PointStoreInvoice
                where 1=1
                and F_ProductKid={0}
                and F_CityKid={1}
                and F_CreateTime>={2}
                and F_CreateTime<{3}
                and F_OrderState = 0
                limit {4}";

            int result = 0;
            try
            {
                using (var db = new DbTour())
                {
                    List<string> kids =await db.Sql(sql1, model.F_ProductKid, model.F_CityKid, model.F_CreateTime, Convert.ToDateTime(model.F_CreateTime).AddDays(1), top).ToList<string>();
                    StringBuilder kidString = new StringBuilder();
                    foreach (string item in kids)
                    {
                        kidString.Append(","+item);
                    }
                    string temp = kidString.ToString();
                    string temp2 = temp.Substring(1);
                    sql = string.Format(sql, temp2,"{0}");
                    result = await db.Sql(sql, model.F_LogisticsName).ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {                
                throw ex;
            }

            return result;
        }

        //----------------------------------------------------------删除指定货物清单---------------------------------------------------
        public static async Task<object> DeleteToDB(string kids)
        {
            kids = kids.Trim();
            string sql = @"
                    update T_PointStoreInvoice
                    set F_IsDeleted=1
                    where kid in({0})
                    ";
            int result = 0;
            using (var db = new DbTour())
            {
                result = await db.Sql(sql, kids).ExecuteNonQuery();
            }
            return result;
        }

        //---------------------------------------------------------获取发货清单汇总数据------------------------------------------------
        public static async Task<PagedList<T_PointStoreInvoice>> FromDBSelectSummary(Pager pager, int authCityKid, int cityKid = -1, int orderState = -1, string logisticsNum = "", string startTime = "", string endTime = "")
        {

            using (var db = DataBaseContext.GetDataBase("reader"))
            {
                var query = db.Table(@"select * from V_PointStoreInvoiceTotal");

                if (authCityKid != 0)
                {
                    query.And(" F_CityKid = {0}", authCityKid);
                }
                else
                {
                    if (cityKid > -1)
                    {
                        query.And(" F_CityKid = {0}", cityKid);
                    }
                }
                if (orderState > -1)
                {
                    query.And(" F_OrderState={0}", orderState);
                }
                if (!string.IsNullOrWhiteSpace(logisticsNum) && Convert.ToDouble(logisticsNum)>0)
                {
                    query.And(" F_LogisticsNum={0}", logisticsNum);
                }
                if (!string.IsNullOrWhiteSpace(startTime))
                {
                    query.And(" F_CreateTime>={0}", Convert.ToDateTime(startTime));
                }
                if (!string.IsNullOrWhiteSpace(endTime))
                {
                    query.And(" F_CreateTime<{0}", Convert.ToDateTime(endTime).AddDays(1));
                }
                //return await query.ToPagedList<T_PointStoreInvoice>(pager.StartNumber, pager.EndNumber," f_createtime desc ");
                try
                {
                    return await query.ToPagedList<T_PointStoreInvoice>(pager.StartNumber, pager.EndNumber, " f_createtime desc");
                }
                catch (Exception ex)
                {                    
                    throw;
                }
                
            }
        }

        //---------------------------------------------------------获取清单明细-------------------------------------------------------
        public static async Task<object> FromDBSelectDetail(Pager pager, int cityKid, int orderState,int ProductKid)
        {
            using (var db = new DbTour())
            {
                var query = db.Table(@"select * from T_PointStoreInvoice");
                query.And(" F_CityKid={0}", cityKid);
                query.And(" F_OrderState=1");
                return await query.ToList<T_PointStoreInvoice>(pager.StartNumber, pager.EndNumber, " F_CreateTime desc");
            }
        }

        //---------------------------------------------------------导出清单明细-------------------------------------------------------
        public static async Task<string> FromDBExportDetail(int cityKid, int orderState, string logisticsNum, string startTime, string endTime)
        {
            using (var db = new DbTour())
            {
                var query = db.Table(@"select * from V_PointStoreInvoiceTotal");
                query.And(" F_CityKid={0}", cityKid);
                query.And(" F_OrderState={0}",orderState);
                if (!string.IsNullOrWhiteSpace(logisticsNum) && Convert.ToDouble(logisticsNum) > 0)
                {
                    query.And(" F_LogisticsNum={0}", logisticsNum);
                }
                if (!string.IsNullOrWhiteSpace(startTime))
                {
                    query.And(" F_CreateTime>={0}", Convert.ToDateTime(startTime));
                }
                if (!string.IsNullOrWhiteSpace(endTime))
                {
                    query.And(" F_CreateTime<{0}", Convert.ToDateTime(endTime).AddDays(1));
                }
                query.OrderBy(" F_CreateTime desc");
                //return await query.ToList<T_PointStoreInvoice>(pager.StartNumber, pager.EndNumber, " F_CreateTime desc");
                DataTable result = await query.ExecuteDataTable();
                DataTable dt = CreateDataTable(result);                
                string path = string.Empty;
                DataTableExcel(dt, string.Format("发货清单（{0}）.xls", DateTime.Now.ToString("yyyy-MM-dd-hhmmss")), out path);
                return path;
            }
        }

        //---------------------------------------------------------从兑换记录提取数据--------------------------------------------------
        /// <summary>
        /// 获取需要发货的兑换记录数据
        /// </summary>
        /// <param name="kids">兑换记录Kid</param>
        /// <returns></returns>
        public static async Task<List<T_PointStoreInvoice>> FromDBGetDespatchData(string kids)
        {
            string sql = @"
                        select  
                            distinct 
                            a.Kid as F_ProductExchangeKid,
                            a.F_ExchangeTime as F_OrderTime,
                            a.F_OrderNo,
                            b.Kid as F_ProductKid,
                            b.F_ProductName,
                            e.F_ProductPrice,
                            IFNULL(h.F_CityKid,0) AS F_CityKid,
                            IFNULL(f.F_Name,'') as F_CityName
                        from T_ProductsExchange_new a 
                        left join T_PointProduct b on a.F_ProductKid=b.Kid        
                        left join T_ProductInventory e on b.Kid=e.F_ProductKid            
                        left join T_OAuthInfo g on a.F_UserToken=g.F_UserToken
                        left join T_Broker h on g.F_BrokerKid=h.kid
                        left join T_ProvinceCityArea f on h.F_CityKid=f.Kid
                        where 1=1
                            and a.F_IsStatus=9
	                        and a.Kid in ({0})
                        ";
            List<T_PointStoreInvoice> model = null;
            using (var db = new DbTour())
            {
                string sql2 = string.Format(sql, kids);
                model = await db.Sql(sql2).ToList<T_PointStoreInvoice>();
                foreach (T_PointStoreInvoice item in model)
                {
                    item.F_ProductCount = 1;
                }
            }
            return model;
        }

        //--------------------------------------------------------提取需要发货的城市、物流单号---------------------------------------------------
        public static async Task<IList<AdminCityExpand>> FromDBGetDespatchCity(T_PointStoreAdminAuthority auth)
        {
            string sql = @"
                select 
	                F_CityKid,
	                F_CityName 
                from T_PointStoreInvoice
                where 1=1
                    {0}
                group by F_CityKid,F_CityName 
                ";
            if (auth.F_CityKid != 0)
            {
                sql = string.Format(sql, " and F_CityKid = " + auth.F_CityKid);
            }
            else
            {
                sql = string.Format(sql, "");
            }
            using (var db = new DbTour())
            {
                return await db.Sql(sql).ToList<AdminCityExpand>();
            }
        }

        public static async Task<IList<string>> FromDBGetDespatchLogisticsNum(T_PointStoreAdminAuthority auth)
        {
            string sql = @"
                select 
	                F_LogisticsNum
                from T_PointStoreInvoice
                where F_LogisticsNum is not null
                {0}
                group by F_LogisticsNum
                ";
            if (auth.F_CityKid!=0)
            {
                sql = string.Format(sql, " and F_CityKid = " + auth.F_CityKid);
            }
            else
            {
                sql = string.Format(sql, "");
            }
            using (var db = new DbTour())
            {
                return await db.Sql(sql).ToList<string>();
            }
        }

        //--------------------------------------------------------导出工具-------------------------------------------------------------
        public static DataTable CreateDataTable(DataTable tb)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("F_CityName", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_CreateTime", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_ProductName", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_ProductCount", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_ProductPrice", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_OrderState", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_LogisticsNum", System.Type.GetType("System.String")));
            foreach (DataRow dr in tb.Rows)
            {
                string status = ConvertStatus(Convert.ToInt32(dr["F_OrderState"].ToString()));
                dt.Rows.Add(new string[] { dr["F_CityName"].ToString(), dr["F_CreateTime"].ToString(), dr["F_ProductName"].ToString(), dr["F_ProductCount"].ToString(), dr["F_ProductPrice"].ToString(), status, dr["F_LogisticsNum"].ToString() });
            }
            return dt;
        }
        public static string ConvertStatus(int num)
        {
            switch (num)
            {
                case 0: return "未发货";
                case 1: return "已发货";
                case 2: return "已确认";
            }
            return "";
        }
        public static void DataTableExcel(DataTable dtData, String FileName, out string path)
        {
            string sitePath = "Document/";
            string FilePathName = GetMapPath(sitePath);
            DirectoryInfo info = Directory.CreateDirectory(FilePathName);
            FilePathName = FilePathName + FileName;


            using (FileStream fs = new FileStream(FilePathName, FileMode.Create))
            {
                byte[] arrWriteData = Encoding.UTF8.GetBytes(GetDataTableHtml(dtData));
                fs.Write(arrWriteData, 0, arrWriteData.Length);
            }
            path = "/" + sitePath + FileName;
        }
        public static string GetDataTableHtml(DataTable grid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=UTF-8\"/>");
            sb.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
            sb.Append("<tr><td>城市</td><td>兑换记录审核通过时间</td><td>商品名称</td><td>商品数量</td><td>金额(元)</td><td>发货状态</td><td>物流单号</td></tr>");
            if (grid.Rows.Count > 0)
            {
                foreach (DataRow row in grid.Rows)
                {
                    sb.Append(@"<tr><td>" + (string.IsNullOrWhiteSpace(row["F_CityName"].ToString()) ? "" : row["F_CityName"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_CreateTime"].ToString()) ? "" : row["F_CreateTime"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_ProductName"].ToString()) ? "" : row["F_ProductName"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_ProductCount"].ToString()) ? "" : row["F_ProductCount"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_ProductPrice"].ToString()) ? "" : row["F_ProductPrice"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_OrderState"].ToString()) ? "" : row["F_OrderState"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_LogisticsNum"].ToString()) ? "" : row["F_LogisticsNum"])+
                         "</td></tr>");
                }
            }
            else
            {
                sb.Append(@"<tr><td colspan='5' style='text-align:center'>未能找到相关信息</td></tr>");
            }
            sb.Append("</table>");

            return sb.ToString();
        }
        public static string GetMapPath(string strPath)
        {
            if (strPath.ToLower().StartsWith("http://"))
            {
                return strPath;
            }
            if (HttpContext.Current != null)
            {
                return HttpContext.Current.Server.MapPath(strPath);
            }
            else //非web程序引用
            {
                strPath = strPath.Replace("/", "\\");
                return AppDomain.CurrentDomain.BaseDirectory + strPath;
            }
        }

    }
}

